/*
****************************************************************************************************************************
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('PrepareSettlement') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	PRINT 'Droping procedure dbo.PrepareSettlement'
	DROP PROCEDURE dbo.PrepareSettlement
END
GO

PRINT 'Creating procedure dbo.PrepareSettlement'
GO

CREATE PROCEDURE dbo.PrepareSettlement
(
	@processDate DATETIME = NULL
	,@currentCulture NVARCHAR(5) = N'en-US'
)
AS
BEGIN
	--BEGIN PREPARE DATES
	SET NOCOUNT ON

	DECLARE @settlementId UNIQUEIDENTIFIER

	-- Clean Settlements Content on Date
	DELETE Cnt FROM dbo.[Settlement] St
	JOIN dbo.SettlementContent AS Cnt ON St.SettlementId = Cnt.SettlementId
	WHERE St.[SettlementDate] = @processDate

	-- Clean Settlement on Date
	DELETE St FROM dbo.[Settlement] St
	WHERE St.[SettlementDate] = @processDate

	SET @settlementId = NEWID()

	INSERT INTO dbo.Settlement( SettlementId,SettlementDate,Comment,[Status])
	VALUES(@settlementId, @processDate, N'', 0)


	IF OBJECT_ID('tempdb..#SettlementDates') IS NOT NULL
	BEGIN
		DROP TABLE #SettlementDates
	END

	CREATE TABLE #SettlementDates
	(
		[PeriodName] NVARCHAR(50) NOT NULL
		,[PeriodBegin] DATETIME NOT NULL
		,[PeriodEnd] DATETIME NOT NULL
		,[PeriodType] INT NOT NULL
	)

	DECLARE @month INT, @quarter INT, @firstDayOfMonth DATETIME

	SET @month = 6
	SET @quarter = 4

	--first day of curren month
	SET @firstDayOfMonth = dateadd(month,datediff(month,0,@processDate),0)
	
	INSERT INTO #SettlementDates([PeriodType],[PeriodName],[PeriodBegin],[PeriodEnd])
	--Insert month
	SELECT 0,N''
		,DATEADD(MM,number,@firstDayOfMonth) AS PeriodBegin
		,DATEADD(dd,-DAY(DATEADD(MM,number,@firstDayOfMonth)),
			DATEADD(mm, 1, DATEADD(MM,number,@firstDayOfMonth))) AS PeriodEnd
	FROM master..spt_values AS Val
	WHERE TYPE='P' AND val.number < @month
	UNION ALL
	--Insert quarter
	SELECT 1,N''
		,DATEADD(qq, DATEDIFF(qq,val.number,DATEADD(qq,val.number,@firstDayOfMonth)), 0) AS PeriodBegin
		,DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq,val.number+1,DATEADD(qq,val.number+1,@firstDayOfMonth)), 0)) AS PeriodEnd
	FROM master..spt_values AS Val
	WHERE TYPE='P' AND val.number < @quarter

	UPDATE #SettlementDates SET 
		[PeriodName] = 'Q'+DATENAME(qq,PeriodBegin)+'-'+RIGHT(DATENAME(year,PeriodBegin),2)
	WHERE DATEDIFF(m,PeriodBegin,PeriodEnd) = 2

	UPDATE #SettlementDates SET 
		[PeriodName] = LEFT(DATENAME(month,PeriodBegin),3)+N'-'+RIGHT(DATENAME(year,PeriodBegin),2)
	WHERE DATEDIFF(m,PeriodBegin,PeriodEnd) = 0

	--END PREPARE DATES

	IF OBJECT_ID('tempdb..#LastTrade') IS NOT NULL
	BEGIN
		DROP TABLE #LastTrade
	END

	SELECT MarketTypeId,LastPrice,ActivityDate
	INTO #LastTrade
	FROM 
	(
		SELECT
			Ent.MarketTypeId, ROW_NUMBER() OVER (PARTITION BY Ent.MarketTypeId ORDER BY Ent.MarketTypeId DESC) AS RowNumber
			,dbo.RemoveTime(ISNULL(TA.ActivityDate,@processDate)) AS ActivityDate
			,ISNULL(TA.Price,0.0) AS LastPrice
		FROM dbo.MarketEntity Ent
		LEFT OUTER JOIN dbo.[TradeActivity] TA 
			ON TA.MarketEntityId = Ent.MarketEntityId 
			AND TA.ActivityDate <= @processDate
	) AS LastTrade
	WHERE LastTrade.RowNumber = 1

	--SELECT * FROM #LastTrade

	INSERT INTO dbo.[SettlementContent]
	(
		[SettlementContentId],[SettlementId],[PeriodType],[MarketTypeId],
		[PeriodName],[PeriodBegin],[PeriodEnd],[Amoint]
	)
	SELECT 
		NEWID()
		,@settlementId
		,Dates.[PeriodType]
		,Trade.[MarketTypeId]
		,Dates.[PeriodName]
		,Dates.[PeriodBegin]
		,Dates.[PeriodEnd]
		,CASE WHEN Trade.ActivityDate 
			BETWEEN Dates.PeriodBegin AND Dates.PeriodEnd 
			THEN Trade.LastPrice 
		ELSE 0 END AS [Amoint]
	FROM #SettlementDates Dates, #LastTrade Trade

	UPDATE Sc SET [Amoint] = [SettlementContentCalc].Amoint
	FROM dbo.[SettlementContent] Sc
	JOIN (
		SELECT Sc.[MarketTypeId],S.SettlementDate, Sc.[Amoint], 
			ROW_NUMBER() OVER (PARTITION BY Sc.[MarketTypeId] 
			ORDER BY Sc.[MarketTypeId],S.SettlementDate DESC) AS RowNumber	
		FROM dbo.[SettlementContent] Sc
		JOIN dbo.Settlement S ON Sc.SettlementId = S.SettlementId		
		WHERE Sc.[SettlementId] != @settlementId AND Sc.[Amoint] != 0.0
	) AS [SettlementContentCalc] ON Sc.MarketTypeId = SettlementContentCalc.MarketTypeId
	WHERE Sc.[SettlementId] = @settlementId AND Sc.[Amoint] = 0.0 AND [SettlementContentCalc].RowNumber = 1
	
	SET NOCOUNT OFF
END
GO